package cs4280.ass2.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

import cs4280.ass2.entity.Movie;

public class MovieDao extends BaseDao {
	private static final Logger logger = Logger.getLogger(MovieDao.class);
	private static final String _SELECT_BY_ID = "select * from MOVIE where ID = ?";
	private static final String _SELECT_ALL = "select * from MOVIE where start_date <= getdate() order by start_date desc";
	private static final String _SELECT_ALL_UPCOMING = "select * from MOVIE where start_date > getdate() order by start_date desc";
	private static final String _INSERT_NEW_MOVIE = "INSERT INTO [MOVIE] (CREATE_DATE, CREATE_USER, MODIFY_DATE, MODIFY_USER, NAME, DESCRIPTION, DIRECTOR, CAST, DURATION, CATEGORY, LANGUAGE, TRAILER_URL, IMAGE_URL, START_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private static final String _UPDATE = "";
	private static final String _DELETE_MOVIE = "DELETE FROM MOVIE WHERE ID = ?";

	// Search Part
	private static String _SEARCH_BY_OPENDATE = "SELECT * FROM MOVIE WHERE OPEN_DATE > "; // >
																							// today()

	private static MovieDao movieDao = null;

	public static MovieDao getInstance() {
		if (movieDao == null)
			movieDao = new MovieDao();
		return movieDao;
	}

	public Movie retrieveMovie(long id) {
		Movie movie = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_BY_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, id);
			result = statement.executeQuery();
			if (result.next()) {
				movie = new Movie();
				movie.setId(result.getLong("id"));
				movie.setName(result.getString("name"));
				movie.setDescription(result.getString("description"));
				movie.setDirector(result.getString("director"));
				movie.setCast(result.getString("cast"));
				movie.setDuration(Integer.parseInt(result.getString("duration")));
				movie.setCategory(result.getString("category"));
				movie.setLanguage(result.getString("language"));
				movie.setTrailerUrl(result.getString("trailer_url"));
				movie.setImageUrl(result.getString("image_url"));
				if (result.getDate("start_date") != null)
					movie.setStartDate(toUtilDate(result
							.getTimestamp("start_date")));
				else
					movie.setStartDate(null);
				movie.setCreateDate(toUtilDate(result
						.getTimestamp("create_date")));
				movie.setCreateUser(result.getLong("create_user"));

				movie.setModifyDate(toUtilDate(result
						.getTimestamp("modify_date")));
				movie.setModifyUser(result.getLong("modify_user"));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveMovie ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return movie;
	}

	public List<Movie> retrieveAllMovie(String attribute, String order) {
		// TODO: work later on.
		return new ArrayList<Movie>();
	}

	public List<Movie> retrieveAllMovie() {
		List<Movie> movieList = new ArrayList<Movie>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL);

			Movie movie;
			while (result.next()) {
				movie = this.retrieveMovie(result.getLong("id"));
				movieList.add(movie);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve Movie ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return movieList;
	}

	public List<Movie> retrieveAllUpcomingMovie() {
		List<Movie> movieList = new ArrayList<Movie>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL_UPCOMING);

			Movie movie;
			while (result.next()) {
				movie = this.retrieveMovie(result.getLong("id"));
				movieList.add(movie);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve Movie ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return movieList;
	}
	
	public void addMovie(Movie movie) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_INSERT_NEW_MOVIE,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			statement.setTimestamp(1, toSQLDate(movie.getCreateDate()));
			statement.setLong(2, movie.getCreateUser());
			statement.setTimestamp(3, toSQLDate(movie.getModifyDate()));
			statement.setLong(4, movie.getModifyUser());

			statement.setString(5, movie.getName());
			statement.setString(6, movie.getDescription());
			statement.setString(7, movie.getDirector());
			statement.setString(8, movie.getCast());
			statement.setInt(9, movie.getDuration());
			statement.setString(10, movie.getCategory());
			statement.setString(11, movie.getLanguage());
			statement.setString(12, movie.getTrailerUrl());
			statement.setString(13, movie.getImageUrl());
			statement.setTimestamp(14, toSQLDate(movie.getStartDate()));

			int rows = statement.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to create movie ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}

	public void updateMovie(Movie movie) {

	}

	public void deleteMovie(long id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_DELETE_MOVIE,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			statement.setLong(1, id);

			// for this deletion, we assumed the provided id is exist.
			int rows = statement.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to delete movie ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}

}
